Switch (expr1, value1, expr2, value2, ..., exprN, valueN)

Basic and Crystal syntax.

Arguments
Returns

One of the values in the value list value1, value2, ...., valueN. The type of the returned value is the same as the type of the values.

Action

The argument list for Switch consists of pairs of expressions and values. Switch evaluates the expressions from left to right, and returns the value associated with the first expression to evaluate to True.

For example, if expression1 is True, the Switch returns value1. If expression1 is False and expression2 is True, then Switch returns value2. If expression1 and expression2 are False and expression3 is True, then Switch returns value3.

If all of the expressions are False, then Switch returns a default value. (The default value returned depends on the type of the values in the value list. For example, if the values are of Number type, the default value is 0 and if the values are of String type, the default value is the empty string ("").)

Typical uses

Switch can be used instead of If-Then-Else operators or Select statements (Basic syntax) and Select expressions (Crystal syntax) in some situations.

One situation where Switch may be better than a control structure is when writing record selection formulas so that they can be pushed down to the database server. See below for an example as well as Using enhanced record selection formulas for an explanation of the techniques involved.

Examples

The following examples are applicable to both Basic and Crystal syntax:

Suppose that a company wants to classify orders into "large", "medium" or "small" based on the order size:

Rem Basic syntax

formula = Switch ({Orders.Order Amount} > 5000, "large", _

                  {Orders.Order Amount} > 1000, "medium", _

                  True, "small")


//Crystal syntax

Switch ({Orders.Order Amount} > 5000, "large",

        {Orders.Order Amount} > 1000, "medium",

        True, "small")

If {Orders.Order Amount} is greater than $5,000 then the formula returns the String value "high". Otherwise, if {Orders.Order Amount} is greater than $1,000, the formula returns the String "medium". Otherwise, the formula returns "small".

Here is an example showing how to use the Switch function for writing efficient record selection formulas that can be pushed down to the database server. Writing this formula using If-Then-Else operators or Select statements (Basic syntax) and Select expressions (Crystal syntax) would not let it be pushed down.

See Using enhanced record selection formulas for further explanation of the techniques involved.

Note:    This example is in Crystal syntax since record selection formulas edited with the Formula Editor must be in Crystal syntax only.

Sample report

A more complete version of the example, with 27 Switch conditions, is provided with the sample report named Date Range Formula.rpt.

Sample reports are located in the Crystal Reports directory under \Samples\En\Reports.

When you preview the report, you supply a reference order date parameter, {?reference date} and a range condition, {?reference condition} to indicate a range of dates around the reference order date. The report is then previewed with only the records whose order dates fall in the specified range.

The entire record selection is performed on the database server. This is because the Switch function and all its arguments can be evaluated before accessing the database.

Note:    

//Crystal syntax record selection formula

{Orders.Order Date} in

Switch

(

    {?reference condition} = "Aged 0 to 30 days",

        ({?reference date} - 30) To {?reference date},

    {?reference condition} = "Aged 31 to 60 days",

        ({?reference date} - 60) To ({?reference date} - 31),

    {?reference condition} = "Aged 61 to 90 days",

        ({?reference date} - 90) To ({?reference date} - 61),

    True, // provide default handling and specify a valid range

        CDate(1899, 12, 30) To CDate(1899, 12, 30)

)

Comments
Related topics

Choose (index, choice1, choice2, ..., choiceN)

IIF (expression, truePart, falsePart)

.Select statements (Basic syntax)

Select expressions (Crystal syntax)



Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com